package com.lb.myeam.core.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lb.myeam.common.utils.DBUtil;
import com.lb.myeam.common.utils.StringUtil;
import com.lb.myeam.core.dao.UserDao;
import com.lb.myeam.core.entity.UserEntity;

public class UserDaoImpl implements UserDao {

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	// login
		public UserEntity login(String account, String pwd) {
			try {
				conn = DBUtil.getConn();
				String sql = "select * from tab_user where account = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, account);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					String password = rs.getString("password");
//					if (StringUtil.getMd5Val(pwd).equals(password)) {
					if (pwd.equals(password)) {
						UserEntity user = new UserEntity();
						user.setAccount(rs.getString("account"));
						user.setName(rs.getString("name"));
						return user;
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DBUtil.close(rs, pstmt, conn);
			}
			return null;
		}
	
	// add
	public boolean add(UserEntity user) {
		try {
			conn = DBUtil.getConn();
			String sql = "insert into TAB_USER values (?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, StringUtil.getUUID());
			pstmt.setString(2, user.getAccount());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getSex());

			java.util.Date birthday = user.getBirthday();
			pstmt.setDate(5, new Date(birthday.getTime()));

			pstmt.setString(6, user.getAddress());
			pstmt.setString(7, user.getTeamId());
			pstmt.setString(8, user.getPassword());
			pstmt.setString(9, user.getName());

			int result = pstmt.executeUpdate();
			return result > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pstmt, conn);
		}
		return false;
	}

	// delete
	public boolean delete(String id) {
		try {
			conn = DBUtil.getConn();
			String sql = "delete from TAB_USER where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			int result = pstmt.executeUpdate();
			return result > 0;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pstmt, conn);
		}
		return false;

	}

	

	// query
	public List<UserEntity> query() {
		
			conn = DBUtil.getConn();
			List<UserEntity> userList = new ArrayList<UserEntity>();
			try {
				String sql = "select * from tab_user";
				pstmt = conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				userList.add(new UserEntity(
						rs.getString(1), 
						rs.getString(2),
						rs.getString(3), 
						rs.getString(4), 
						rs.getDate(5), 
						rs.getString(6), 
						rs.getString(7),
						rs.getString(8), 
						rs.getString(9)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pstmt, conn);
		}
		return userList;
		
	}
	
	//findById
	public UserEntity findById(String id) {
		try {
			conn = DBUtil.getConn();
			String sql = "select * from tab_user where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				UserEntity user = new UserEntity();
				user.setAccount(rs.getString("account"));
				user.setName(rs.getString("name"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pstmt, conn);
		}
		return null;
	}

	// update
	public boolean update(UserEntity user) {
		try {
			conn=DBUtil.getConn();
			String sql = "update tab_user set account=\'"+user.getAccount()+ 
					"\',email=\'"+user.getEmail()+
					"\',sex=\'"+user.getSex()+
					"\',birthday="+"sysdate"+
					"\',address=\'"+user.getAddress()+
					"\',team_id=\'"+user.getTeamId()+
					"\',password=\'"+user.getPassword()+
					"\',name=\'"+user.getName()+
					"\' where id=\'"+user.getId()+"\'";
			pstmt=conn.prepareStatement(sql);
			int result=pstmt.executeUpdate();
			return result>0;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pstmt, conn);
		}
		
		return false;
	}
	
	public boolean isExist(String account) {
		try {
			conn = DBUtil.getConn();
			String sql = "select * from tab_user where account = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,account);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(rs, pstmt, conn);
		}
		return true;
	}


}
